import sys
import os

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))
from aws_model.athena_submit import submit
from sql_lambda.delete import delete_data
from base_class.inspect_logging import print_to_log
import datetime
import json
from aws_model.aws_stepfunc import send_task_success, send_task_failure
from project_utils.mysql_conncet import ReadFromMysql
from config import *


def result_gnscsyl(event):
    date = event.get('p_date')
    if not date:
        date = datetime.datetime.today().strftime('%Y-%m-%d')
    table_name = sys._getframe().f_code.co_name
    delete_data(result_bucket,table_name, date)

    # 获取mysql func_data功能信息
    mysql = ReadFromMysql(mysqlhost, user, password, 3306)
    mysql.makepool(database)
    func = mysql.read_from_mysql(f"select * from {func_table} where type = 'FRIDGE' ")
    func_sql = ""
    for row in func:
        func_sql += "'" + row["func_name"] + "|" + "',"
        func_sql += f'''if(sum({get_lower_case_name(row["func_field"]) + "_on"}) > 0, '1','0'),',','''

    func_sql = func_sql[0:func_sql.rindex(''',',',''')]

    sql = f'''insert into fridge_biz_result.result_gnscsyl
                (
                    select
                        model,
                        function_name,
                        count(1) as report_num,
                        count_if(used = '1') as function_num,
                        format_datetime(cast ('{date}' as date) - interval '1' month, 'YYYY-MM') as time
                    from
                        (
                        select
                            upper(model) as model,
                            sn,
                            func,
                            split_part(func,'|',1) as function_name,
                            split_part(func,'|',2) as used
                        from
                            (
                            select
                                model,
                                sn,
                                concat({func_sql}) as funcs
                            from
                                dws.fridge_status_summary_m
                            where
                                p_date >= format_datetime(cast ('{date}' as date) - interval '1' year,'yyyy-MM')
                            and
                                p_date < format_datetime(cast ('{date}' as date),'yyyy-MM')
                            group by
                                model,sn
                            ) t
                        cross join unnest(split(funcs,',')) as f(func)
                        ) t2
                    group by
                        t2.model,function_name
)'''

    athena_response = submit(sql)
    print_to_log("athena_response: ", athena_response)
    output = {"msg": f"{table_name} submit ok", "Payload": {}}
    output = json.dumps(output)
    step_response = send_task_success(tasktoken, output)
    print_to_log("step_response: ", step_response)


def get_lower_case_name(text):
    lst = []
    for index, char in enumerate(text):
        if char.isupper() and index != 0:
            lst.append("_")
        lst.append(char)
    return "".join(lst).lower()


if __name__ == '__main__':
    try:
        event = json.loads(sys.argv[1])
        tasktoken = event.get('TaskToken')
        result_gnscsyl(event)
    except Exception as e:
        print_to_log("athena_submit 失败")
        print_to_log("原因： ", str(e))
        send_task_failure(str(e), f'执行{sys._getframe().f_code.co_filename}错误', tasktoken)
